﻿package com.golden.base.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import com.golden.base.util.BaseException;
import com.golden.base.util.JNDINames;

public class DAOBase {

	private ConnectionManager mrg = null;

	public Connection getConnection(){
		if(mrg==null){
			mrg = ConnectionManager.getInstance();
		}
		return mrg.getConnection(JNDINames.POOLNAME_GCMIS);
	}

	//将指定的数据库链接标记为空闲
	public void freeConnection(Connection conn){
		if(mrg!=null){
			mrg.freeConnection(JNDINames.POOLNAME_GCMIS, conn);
		}
	}
	
	public boolean duplicateData(String tableName, String attName, String value)throws BaseException{
		try{
			String sql = "select count(*) from " + tableName + " where " + attName + "='" + value + "'" ;
			if(this.getResultCount(sql)>=1){
				return true;
			}
			return false;
		}catch(BaseException e){
			 throw new BaseException("判断数据是否重复（一个字段）出错：" + e);
		}
	}
	
	public boolean duplicateData(String tableName, String attName1, String value1, String attName2, String value2)throws BaseException{
		try{
			String sql = "select count(*) from " + tableName + " where " + attName1 + "='" + value1 + "' and  " + attName2 + "='" + value2 + "'" ;
			if(this.getResultCount(sql)>=1){
				return true;
			}
			return false;
		}catch(BaseException e){
			 throw new BaseException("判断数据是否重复（两个字段）出错：" + e);
		}
	}

	public boolean duplicateData(String tableName, String attName1, String value1, String attName2, Date value2)throws BaseException{
		try{
			String sql = "select count(*) from " + tableName + " where " + attName1 + "=? and  " + attName2 + "=? " ;
			int count = 0;
			Connection conn = this.getConnection();
			PreparedStatement ps = null;
			ResultSet rs = null;
			if(conn!=null){
				try{
					ps = conn.prepareStatement(sql);
					ps.setString(1,value1);
					ps.setDate(2, value2);
					rs = ps.executeQuery();
					if(rs.next()){
						count = rs.getInt(1);
					}
				}catch(Exception e){
					 throw new BaseException("获取记录总数出错：" + e);
				}finally{
					try{
						rs.close();
					}catch(Exception e){}
					try{
						ps.close();
					}catch(Exception e){}
					try{
						this.freeConnection(conn);
					}catch(Exception e){}
				}
			}else{
				try{
					rs.close();
				}catch(Exception e){}
				try{
					ps.close();
				}catch(Exception e){}
				throw new BaseException("获取记录总数出错：数据库链接为空");
			}
			return count>0;
		}catch(BaseException e){
			 throw new BaseException("判断数据是否重复（两个字段）出错：" + e);
		}
	}

	public boolean duplicateData(String tableName, String attName1, String value1, String attName2, String value2, String attName3, String value3)throws BaseException{
		try{
			String sql = "select count(*) from " + tableName + " where " + attName1 + "='" + value1 + "' and  " + attName2 + "='" + value2 + "' and  " + attName3 + "='" + value3 + "'" ;
			if(this.getResultCount(sql)>=1){
				return true;
			}
			return false;
		}catch(BaseException e){
			 throw new BaseException("判断数据是否重复（三个字段）出错：" + e);
		}
	}
	
	public boolean duplicateData(String tableName, String attName1, String value1, String attName2, Date value2, String attName3, Date value3)throws BaseException{
		try{
			String sql = "select count(*) from " + tableName + " where " + attName1 + "=? and  " + attName2 + "=? and  " + attName3 + "=? " ;
			int count = 0;
			Connection conn = this.getConnection();
			PreparedStatement ps = null;
			ResultSet rs = null;
			if(conn!=null){
				try{
					ps = conn.prepareStatement(sql);
					ps.setString(1, value1);
					ps.setDate(2, value2);
					ps.setDate(3, value3);
					rs = ps.executeQuery();
					if(rs.next()){
						count = rs.getInt(1);
					}
				}catch(Exception e){
					 throw new BaseException("获取记录总数出错：" + e);
				}finally{
					try{
						rs.close();
					}catch(Exception e){}
					try{
						ps.close();
					}catch(Exception e){}
					try{
						this.freeConnection(conn);
					}catch(Exception e){}
				}
			}else{
				try{
					rs.close();
				}catch(Exception e){}
				try{
					ps.close();
				}catch(Exception e){}
				throw new BaseException("获取记录总数出错：数据库链接为空");
			}
			return count>0;
		}catch(BaseException e){
			 throw new BaseException("判断数据是否重复（两个字段）出错：" + e);
		}
	}

	public boolean duplicateData(String tableName, String attName1, String value1, String attName2, String value2, String attName3, Date value3, String attName4, Date value4)throws BaseException{
		try{
			String sql = "select count(*) from " + tableName + " where " + attName1 + "=? and  " + attName2 + "=?  and  " + attName3 + "=?  and  " + attName4 + "=? " ;
			int count = 0;
			Connection conn = this.getConnection();
			PreparedStatement ps = null;
			ResultSet rs = null;
			if(conn!=null){
				try{
					ps = conn.prepareStatement(sql);
					ps.setString(1, value1);
					ps.setString(2, value2);
					ps.setDate(3, value3);
					ps.setDate(4, value4);
					rs = ps.executeQuery();
					if(rs.next()){
						count = rs.getInt(1);
					}
				}catch(Exception e){
					 throw new BaseException("获取记录总数出错：" + e);
				}finally{
					try{
						rs.close();
					}catch(Exception e){}
					try{
						ps.close();
					}catch(Exception e){}
					try{
						this.freeConnection(conn);
					}catch(Exception e){}
				}
			}else{
				try{
					rs.close();
				}catch(Exception e){}
				try{
					ps.close();
				}catch(Exception e){}
				throw new BaseException("获取记录总数出错：数据库链接为空");
			}
			return count>0;
		}catch(BaseException e){
			 throw new BaseException("判断数据是否重复（两个字段）出错：" + e);
		}
	}

	public int getResultCount(String sql)throws BaseException{
		Connection conn = this.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		if(conn!=null){
			try{
				int count = 0;
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				if(rs.next()){
					count = rs.getInt(1);
				}
				return count;
			}catch(Exception e){
				 throw new BaseException("获取记录总数出错：" + e);
			}finally{
				try{
					rs.close();
				}catch(Exception e){}
				try{
					ps.close();
				}catch(Exception e){}
				try{
					this.freeConnection(conn);
				}catch(Exception e){}
			}
		}else{
			try{
				rs.close();
			}catch(Exception e){}
			try{
				ps.close();
			}catch(Exception e){}
			throw new BaseException("获取记录总数出错：数据库链接为空");
		}
	}

	public String[][] getDataResultArray(String resultSql, int count, int size) throws BaseException{
		Connection conn = this.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		if(conn!=null){
			try{
				String[][] result = new String[count][size];
				ps = conn.prepareStatement(resultSql);
				rs = ps.executeQuery();
				int index = 0;
				while(rs.next()){
					for(int indexAtt=0; indexAtt<size; indexAtt++){
						result[index][indexAtt] = rs.getString(indexAtt+1);
					}
					index++;
				}
				return result;
			}catch(Exception e){
				 throw new BaseException("获取数据记录出错：" + e);
			}finally{
				try{
					rs.close();
				}catch(Exception e){}
				try{
					ps.close();
				}catch(Exception e){}
				try{
					this.freeConnection(conn);
				}catch(Exception e){}
			}
		}else{
			try{
				rs.close();
			}catch(Exception e){}
			try{
				ps.close();
			}catch(Exception e){}
			throw new BaseException("获取数据记录出错：数据库链接为空");
		}
	}

	public int getDayMaxSizeByTableName(String tableName) throws BaseException{
		Connection conn = this.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		if(conn!=null){
			try{
				int count = 0;
				String sql = "select t.record_number " +
						"from t_a_a_tablekey t " +
						"where t.table_name= ? and t.create_date_str= ? ";
				DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
				String createDateStr = df.format(Calendar.getInstance().getTime());
				ps = conn.prepareStatement(sql);
				ps.setString(1, tableName);
				ps.setString(2, createDateStr);
				rs = ps.executeQuery();
				//如果能取到数据,则说明该表当天已经插入了数据，取出已有的记录条数，加1作为结果返回
				if(rs.next()){
					count = rs.getInt(1) + 1;
					rs.close();
					ps.close();
					//返回结果之前要把该表对应的已有记录条数加1
					String sqlUpdate = "update t_a_a_tablekey t set " +
							"t.record_number=t.record_number+1 " +
							"where t.table_name=? and t.create_date_str=? ";
					ps = conn.prepareStatement(sqlUpdate);
					ps.setString(1, tableName);
					ps.setString(2, createDateStr);
					ps.executeUpdate();
					rs.close();
					ps.close();
				}
				//如果取不到，则说明该表当天还没有插入记录，先在记录生成记录表中插入一条该表的数据
				else{
					rs.close();
					ps.close();
					String sqlInsert = "insert into t_a_a_tablekey t ( " +
							"t.table_name, t.create_date_str, t.record_number " +
							") values ( ?, ?, ?) ";
					ps = conn.prepareStatement(sqlInsert);
					ps.setString(1, tableName);
					ps.setString(2, createDateStr);
					ps.setInt(3, 0);
					ps.executeUpdate();
					count = 0;
					rs.close();
					ps.close();
				}
				return count;
			}catch(Exception e){
				 throw new BaseException("根据表名获取当日已有记录条数出错：" + e);
			}finally{
				try{
					rs.close();
				}catch(Exception e){}
				try{
					ps.close();
				}catch(Exception e){}
				try{
					this.freeConnection(conn);
				}catch(Exception e){}
			}
		}else{
			try{
				rs.close();
			}catch(Exception e){}
			try{
				ps.close();
			}catch(Exception e){}
			throw new BaseException("根据表名获取当日已有记录条数出错：数据库链接为空");
		}
	}
	
	public int getSelfAddValue(String tableName, String attName) throws BaseException{
		Connection conn = this.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		if(conn!=null){
			try{
				int count = 0;
				String sql = "select max( " + attName + " ) from " + tableName;
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				if(rs.next()){
					count = rs.getInt(1) + 1;
					rs.close();
					ps.close();
				}else{
					count = 1;
				}
				return count;
			}catch(Exception e){
				 throw new BaseException("根据表名获取当指定字段的自增值出错：" + e);
			}finally{
				try{
					rs.close();
				}catch(Exception e){}
				try{
					ps.close();
				}catch(Exception e){}
				try{
					this.freeConnection(conn);
				}catch(Exception e){}
			}
		}else{
			try{
				rs.close();
			}catch(Exception e){}
			try{
				ps.close();
			}catch(Exception e){}
			throw new BaseException("根据表名获取当指定字段的自增值出错：数据库链接为空");
		}
	}
	
	public int getSelfAddValue(String tableName, String attName, String attConditionName, String value) throws BaseException{
		Connection conn = this.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		if(conn!=null){
			try{
				int count = 0;
				String sql = "select max( " + attName + " ) from " + tableName +
						" where " + attConditionName + "='" + value + "'";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				if(rs.next()){
					count = rs.getInt(1) + 1;
					rs.close();
					ps.close();
				}else{
					count = 1;
				}
				return count;
			}catch(Exception e){
				 throw new BaseException("根据表名和条件字段的值获取当指定字段的自增值出错：" + e);
			}finally{
				try{
					rs.close();
				}catch(Exception e){}
				try{
					ps.close();
				}catch(Exception e){}
				try{
					this.freeConnection(conn);
				}catch(Exception e){}
			}
		}else{
			try{
				rs.close();
			}catch(Exception e){}
			try{
				ps.close();
			}catch(Exception e){}
			throw new BaseException("根据表名和条件字段的值获取当指定字段的自增值出错：数据库链接为空");
		}
	}
	
	protected void close(ResultSet rs , Statement st, Connection conn) throws BaseException{
		try {
			try{
				rs.close();
			}catch(Exception e){}
			try{
				st.close();
			}catch(Exception e){}
			try{
				this.freeConnection(conn);
			}catch(Exception e){}
		} catch (Exception e) {
			throw new BaseException("关闭连接发生错误");
		}
	}
}
